
 
foreach Y of numlist 2000/2016{

use "W:\workplace\data\police_workplace.dta", clear
rename re_shnro shnro
sort shnro 

local lag_year = `Y' - 1
	
keep if year_event==`Y'

**for defendants**
* merge with relevant year fleed data
merge m:1 shnro using "D:\ready-made\FLEED_TOTAL\2016\fleed_kokonais_`Y'.dta", keepusing(shnro vuosi sykstun syrtun ptoim1 tyotu svatva toimiala ammattikoodi tyokk kunta ututku ika tyrtu svatvp) keep(3)
drop _merge
rename sykstun sykstun_current_year

* merge with the establishment in the previous year, as we use this to define workplace violence (see paper for more details)
merge m:1 shnro using "D:\ready-made\FLEED_TOTAL\2016\fleed_kokonais_`lag_year'.dta", keepusing(sykstun) keep(3)
drop _merge
rename sykstun sykstun_lag
rename sykstun_current_year sykstun 

save "W:\workplace\data\police_DV_workplace_defendant_`Y'.dta", replace

foreach X in shnro vuosi sykstun syrtun ptoim1 tyotu svatva toimiala ammattikoodi tyokk kunta ututku ika tyrtu svatvp sykstun_lag{
rename `X' defendant_`X'	
}
rename ao_shnro shnro
sort shnro

**for victim**
* merge with relevant year fleed data
merge m:1 shnro using "D:\ready-made\FLEED_TOTAL\2016\fleed_kokonais_`Y'.dta", keepusing(shnro vuosi sykstun syrtun ptoim1 tyotu svatva toimiala ammattikoodi tyokk kunta ututku ika tyrtu svatvp) keep(3)
drop _merge
rename sykstun sykstun_current_year

* merge with the establishment in the previous year, as we use this to define workplace violence (see paper for more details)
merge m:1 shnro using "D:\ready-made\FLEED_TOTAL\2016\fleed_kokonais_`lag_year'.dta", keepusing(sykstun) keep(3)
drop _merge
rename sykstun sykstun_lag
rename sykstun_current_year sykstun 

foreach X in shnro vuosi sykstun syrtun ptoim1 tyotu svatva toimiala ammattikoodi tyokk kunta ututku ika tyrtu svatvp sykstun_lag{
rename `X' victim_`X'	
}

* generate workplace violence dummy if both defendant and victim work in the same plant when the crime is committed or the year before (see paper for more details)
gen wp_crime=1 if defendant_sykstun==victim_sykstun & defendant_sykstun!=""
replace wp_crime=1 if defendant_sykstun_lag==victim_sykstun_lag & defendant_sykstun_lag!=""
replace wp_crime=0 if wp_crime==.
keep if wp_crime==1


preserve  	
save "W:\workplace\data\police_DV_workplace_defendant_`Y'.dta", replace
restore 

save "W:\workplace\data\police_DV_workplace_victim_`Y'.dta", replace

}


foreach Y of numlist 2017/2018{
use "W:\workplace\data\police_workplace.dta", clear
rename re_shnro shnro
sort shnro 

local lag_year = `Y' - 1
	
keep if year_event==`Y'

gen vuosi=year_event

**for defendants**
* merge with relevant FOLK data (Fleed not available for later years)
merge m:1 shnro vuosi using "D:\ready-made\FOLK_tulo_11a\folk_20112019_tua_tulo21tot_1.dta", keepusing(shnro tyotu tyrtu) keep(3)
drop _merge

* merge with relevant FOLK data (Fleed not available for later years)
merge m:1 shnro vuosi using "D:\ready-made\FOLK_perus_11a\folk_20112020_tua_perus21tot_1.dta", keepusing(shnro ptoim1 ammattikoodi_k ika) keep(3)
drop _merge


* merge with relevant FOLK data (Fleed not available for later years)
merge m:1 shnro vuosi using "D:\ready-made\FOLK_tutk_11a\folk_20112019_tua_tutk21tot_1.dta", keepusing(shnro koulk) keep(3)
drop _merge
rename koulk ututku


* merge with relevant FOLK data (Fleed not available for later years)
merge m:1 shnro vuosi using "D:\ready-made\FOLK_tkt_11a\folk_20112018_tua_tkt21tot_1", keepusing(shnro sykstun ammattikoodi) keep(3)
drop _merge

rename sykstun sykstun_current_year



* merge with last year plant
replace vuosi=vuosi-1 
merge m:1 shnro vuosi using "D:\ready-made\FOLK_tkt_11a\folk_20112018_tua_tkt21tot_1", keepusing(shnro sykstun) keep(3)
drop _merge
rename sykstun sykstun_lag
rename sykstun_current_year sykstun

save "W:\workplace\data\police_DV_workplace_defendant_`Y'.dta", replace



foreach X in shnro vuosi sykstun ptoim1 tyotu ammattikoodi ammattikoodi_k ututku ika tyrtu sykstun_lag{
rename `X' defendant_`X'	
}
rename ao_shnro shnro
sort shnro


gen vuosi=year_event

**for victim**
* merge with relevant FOLK data (Fleed not available for later years)
merge m:1 shnro vuosi using "D:\ready-made\FOLK_tulo_11a\folk_20112019_tua_tulo21tot_1.dta", keepusing(shnro tyotu tyrtu) keep(3)
drop _merge

* merge with relevant FOLK data (Fleed not available for later years)
merge m:1 shnro vuosi using "D:\ready-made\FOLK_perus_11a\folk_20112020_tua_perus21tot_1.dta", keepusing(shnro ptoim1 ammattikoodi_k ika) keep(3)
drop _merge

* merge with relevant FOLK data (Fleed not available for later years)
merge m:1 shnro vuosi using "D:\ready-made\FOLK_tutk_11a\folk_20112019_tua_tutk21tot_1.dta", keepusing(shnro koulk) keep(3)
drop _merge
rename koulk ututku


* merge with relevant FOLK data (Fleed not available for later years)
merge m:1 shnro vuosi using "D:\ready-made\FOLK_tkt_11a\folk_20112018_tua_tkt21tot_1", keepusing(shnro sykstun ammattikoodi) keep(3)
drop _merge

rename sykstun sykstun_current_year


* merge with relevant FOLK data (Fleed not available for later years)
replace vuosi=vuosi-1 
merge m:1 shnro vuosi using "D:\ready-made\FOLK_tkt_11a\folk_20112018_tua_tkt21tot_1", keepusing(shnro sykstun) keep(3)
drop _merge
rename sykstun sykstun_lag
rename sykstun_current_year sykstun

foreach X in shnro vuosi sykstun ptoim1 tyotu ammattikoodi ammattikoodi_k ututku ika tyrtu sykstun_lag{
rename `X' victim_`X'	
}

* generate workplace violence dummy if both defendant and victim work in the same plant when the crime is committed or the year before (see paper for more details)
gen wp_crime=1 if defendant_sykstun==victim_sykstun & defendant_sykstun!=""
replace wp_crime=1 if defendant_sykstun_lag==victim_sykstun_lag & defendant_sykstun_lag!=""
replace wp_crime=0 if wp_crime==.
keep if wp_crime==1


preserve  
save "W:\workplace\data\police_DV_workplace_defendant_`Y'.dta", replace
restore 

save "W:\workplace\data\police_DV_workplace_victim_`Y'.dta", replace

}

